﻿using Meiam.System.Interfaces.IService;
using Meiam.System.Model.Entity;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Meiam.System.Interfaces
{
    public class EquipmentDropBoxService : SqlSugarBase<EquipmentDropBox>, IEquipmentDropBoxService 
    {
        public EquipmentDropBoxService(IConfiguration _configuration)
        {
            base.connectionString = _configuration.GetConnectionString("DB1");
        }

        /// <summary>
        /// 机检确认报表(数量汇总)
        /// </summary>
        /// <returns></returns>
        public List<EquipmentDropBox> GetEquipmentDropBoxNumSummary()
        {
            string startTime = DateTime.Now.AddHours(-2).ToString("yyyy-MM-dd HH:mm:ss");
            string endTime =  DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            string sql = @"SELECT
	altb.CUFXEQU FXRESOURCENAME,
    COUNT(ccd.CONTAINERNAME) CONTAINERNAMECOUNT,
	SUM((CASE
	WHEN ccd.CUCONTAINERGRADERRECORDNAME LIKE 'RNP%' THEN 0
	WHen ccd.CUCONTAINERGRADERRECORDNAME LIKE 'RCF%' THEN 0
	ELSE TO_NUMBER(altb.CUREALQTY)
	END
	)) THEORYPIECE,
	SUM(altb.CUPIECECOUNT) INPIECECOUNT,
	SUM(NVL(ccd.CUAPLUSGRADER1,0)) CUAPLUSGRADER1,
	SUM(NVL(ccd.CUAPLUSGRADER2,0)) CUAPLUSGRADER2,
	SUM(NVL(ccd.CUAPLUSGRADER3,0)) CUAPLUSGRADER3,
	SUM(NVL(ccd.CUAGRADERWP,0)) CUAGRADERWP,
	SUM(NVL(ccd.CUAGRADERBB,0)) CUAGRADERBB,
	SUM(NVL(ccd.CUAGRADERXH,0)) CUAGRADERXH,
	SUM(NVL(ccd.CUAGRADERQQ,0)) CUAGRADERQQ,
	SUM(NVL(ccd.CUAGRADER,0)) CUAGRADER,
	SUM(NVL(ccd.CUBGRADERGL,0)) CUBGRADERGL,
	SUM(NVL(ccd.CUBGRADERXH,0)) CUBGRADERXH,
	SUM(NVL(ccd.CUBGRADERQQ,0)) CUBGRADERQQ,
	SUM(NVL(ccd.CUBGRADERHB,0)) CUBGRADERHB,
	SUM(NVL(ccd.CUBGRADERTTV,0)) CUBGRADERTTV,
	SUM(NVL(ccd.CUBGRADERDZ,0)) CUBGRADERDZ,
	SUM(NVL(ccd.CUBGRADERCC,0)) CUBGRADERCC,
	SUM(NVL(ccd.CUCGRADERBG,0)) CUCGRADERBG,
	SUM(NVL(ccd.CUCGRADEDIANZU,0)) CUCGRADEDIANZU,
	SUM(NVL(ccd.CUCGRADERCC,0)) CUCGRADERCC,
	SUM(NVL(ccd.CUDGRADERCK,0)) CUDGRADERCK,
	SUM(NVL(ccd.CUDGRADERYL,0)) CUDGRADERYL,
	SUM(NVL(ccd.CUGRADERQJ,0)) CUGRADERQJ,
	SUM(NVL(ccd.CUUNKNOWGRADER,0)) CUUNKNOWGRADER,
	SUM(NVL(ccd.cuGraderDD,0)) cuGraderDD,
	SUM(NVL(ccd.cuAGraderHP,0)) cuAGraderHP,
	SUM(NVL(ccd.cuBGraderHP,0)) cuBGraderHP,
	SUM(NVL(ccd.cuDGraderPP,0)) cuDGraderPP,
	SUM(NVL(ccd.cuDTTV,0)) cuDTTV,
	SUM(NVL(ccd.cuDHouBo,0)) cuDHouBo
FROM CUCONTAINERGRADERRECORD ccd
LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID
WHERE ccd.SPECNAME IN ('分选')
AND ccd.RECORDTIME>= to_date('" + startTime+ @"','yyyy-MM-dd hh24:mi:ss') AND ccd.RECORDTIME<= to_date('" + endTime+ @"','yyyy-MM-dd hh24:mi:ss') 
 GROUP BY altb.CUFXEQU
 ORDER BY DECODE(SUBSTR(altb.CUFXEQU, 0, 1), 'Q', 1, 'W', 2, '2', 3, '3', 4) ASC,
				 SUBSTR(altb.CUFXEQU, -2, 2) ASC 
";
            return base.Query(sql);
        }

       
    }
}
